--------------------------------------------------------------------------------
-- @name: join predicate pushing
-- @author: dion cho
-- @note: test case for the join predicate pushing and group by
--------------------------------------------------------------------------------

@capture_on

set pages 100
set trimspool on
set timing off

drop table t1 purge;
drop table t2 purge;
drop table t3 purge;

create table t1(c1 int, c2 int); 
create table t2(c1 int, c2 int);
create table t3(c1 int, c2 int);

insert into t1 select level, level from dual connect by level <= 10000;
insert into t2 select level, level from dual connect by level <= 10000;
insert into t3 select level, level from dual connect by level <= 10000;

create index t1_n1 on t1(c1);
create index t2_n1 on t2(c1);
create index t3_n1 on t3(c1);

@gather t1
@gather t2
@gather t3

-- case1: group by and join predicate pushing
-- 10.2.0.1
@conn 1021
select * from v$version where rownum = 1;

explain plan for
select 
	*
from t1, t2,
	(select c1, sum(c2) from t3 group by c1 order by c1) v1
where 
	t1.c1 = t2.c1
	and t2.c1 = v1.c1
  and t1.c2 = 1
;

@plan

-- 11.1.0.6
@conn 1106
select * from v$version where rownum = 1;
explain plan for
select 
	*
from t1, t2,
	(select c1, sum(c2) from t3 group by c1 order by c1) v1
where 
	t1.c1 = t2.c1
	and t2.c1 = v1.c1
  and t1.c2 = 1
;

@plan

-- 11.1.0.6
@para jppd


explain plan for
select /*+ opt_param('_optimizer_extend_jppd_view_types', 'false') */
	*
from t1, t2,
	(select c1, sum(c2) from t3 group by c1 order by c1) v1
where 
	t1.c1 = t2.c1
	and t2.c1 = v1.c1
  and t1.c2 = 1
;

@plan

@conn 1021
alter session set tracefile_identifier = 'gby_jppd_fail';
@trace_on 10053 1
select 
	*
from t1, t2,
	(select c1, sum(c2) from t3 group by c1 order by c1) v1
where 
	t1.c1 = t2.c1
	and t2.c1 = v1.c1
  and t1.c2 = 1
;
@trace_off
@trace_file
ho copy &trace_file gby_jppd_fail.trc

@conn 1106
alter session set tracefile_identifier = 'gby_jppd_success';
@trace_on 10053 1
select 
	*
from t1, t2,
	(select c1, sum(c2) from t3 group by c1 order by c1) v1
where 
	t1.c1 = t2.c1(+)
	and t2.c1 = v1.c1(+)
  and t1.c2 = 1
;
@trace_off
@trace_file
ho copy &trace_file gby_jppd_success.trc

@diff gby_jppd_fail.trc gby_jppd_success.trc


-- case2: union all and join predicate pushing
explain plan for
select 
	v1.c1, t3.c2
from 
	(
		select c1, c2 from t1
		union all
	 	select c1, c2 from t2
	) v1, t3
where v1.c1 = t3.c1
			and t3.c2 between 1 and 10
;

@plan

explain plan for
select /*+ no_push_pred(v1) */
	v1.c1, t3.c2
from 
	(
		select c1, c2 from t1
		union all
	 	select c1, c2 from t2
	) v1, t3
where v1.c1 = t3.c1
			and t3.c2 between 1 and 10
;

@plan

alter session set tracefile_identifier = 'union_predicate_push_success';
@trace_on 10053 1
select 
	v1.c1, t3.c2
from 
	(
		select c1, c2 from t1
		union all
	 	select c1, c2 from t2
	) v1, t3
where v1.c1 = t3.c1
			and t3.c2 between 1 and 10
;
@trace_off
@trace_file
ho copy &trace_file union_predicate_push_success.trc


-- but this fails!
explain plan for
select 
	v1.c1, t3.c2
from 
	(
		select c1, c2 from t1
		union all
	 	select c1, c2 from t2
	) v1, t3
where v1.c1 = t3.c1
			and t3.c2 between 1 and 100
;

@plan

-- enable it with hints!
explain plan for
select /*+ first_rows */
	v1.c1, t3.c2
from 
	(
		select c1, c2 from t1
		union all
	 	select c1, c2 from t2
	) v1, t3
where v1.c1 = t3.c1
			and t3.c2 between 1 and 100
;

@plan

explain plan for
select /*+ first_rows(1) */
	v1.c1, t3.c2
from 
	(
		select c1, c2 from t1
		union all
	 	select c1, c2 from t2
	) v1, t3
where v1.c1 = t3.c1
			and t3.c2 between 1 and 100
;

@plan



explain plan for
select /*+ leading(t3) use_nl(v1) */
	v1.c1, t3.c2
from 
	(
		select c1, c2 from t1
		union all
	 	select c1, c2 from t2
	) v1, t3
where v1.c1 = t3.c1
			and t3.c2 between 1 and 100
;

@plan

alter session set tracefile_identifier = 'union_predicate_push_fail';
@trace_on 10053 1
select 
	v1.c1, t3.c2
from 
	(
		select c1, c2 from t1
		union all
	 	select c1, c2 from t2
	) v1, t3
where v1.c1 = t3.c1
			and t3.c2 between 1 and 100
;
@trace_off
@trace_file
ho copy &trace_file union_predicate_push_fail.trc

@diff union_predicate_push_success.trc union_predicate_push_fail.trc

set timing on

@capture_off

